# -*- coding: utf-8 -*-

import connect_config
import pymysql
from dbutils.pooled_db import PooledDB


class MySQLHandler:

    def __init__(self):
        config = connect_config.ReadMySQLConfig()
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=20,
            blocking=True,
            host=config.getDBHost(),
            user=config.getDBUser(),
            password=config.getDBPassword(),
            port=int(config.getDBPort()),
            database=config.getDBDatabase(),
            charset=config.getDBCharset()
        )

    def connect(self):
        self._conn = self.pool.connection()
        self._cursor = self._conn.cursor()
        return self._conn, self._cursor


    def close(self, cursor, conn):
        cursor.close()
        conn.close()

    def test_connect(self):
        conn, cursor = self.connect()
        res = cursor.execute("SELECT NOW()")
        print(res)
        self.close(conn, cursor)

    def fetch_one(self, sql, args=None):
        conn, cursor = self.connect()
        cursor.execute(sql, args)
        res = cursor.fetchone()
        self.close(conn, cursor)
        return res

    def fetch_all(self, sql, args=None):
        conn, cursor = self.connect()
        cursor.execute(sql, args)
        res = cursor.fetchall()
        self.close(conn, cursor)
        return res

    def insert_one(self, sql, args=None):
        conn, cursor = self.connect()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close(conn, cursor)
        return res

    def insert_batch(self, sql, args=None):
        conn, cursor = self.connect()
        res = cursor.executemany(sql, args)
        conn.commit()
        self.close(conn, cursor)
        return res

    def insert_batch_df(self, df, table_name, symbol = '000001'):
        conn, cursor = self.connect()
        print('symbol:' + symbol)
        # 获取列名
        columns = ', '.join(df.columns)

        # 构建插入数据的SQL语句
        sql = f"INSERT INTO {table_name} ({columns}) VALUES ({', '.join(['%s'] * len(df.columns))})"

        # 将DataFrame数据转换为元组列表
        data = [tuple(x) for x in df.values]
        # print('data:' + data)
        # 执行批量插入
        res = cursor.executemany(sql, data)
        conn.commit()
        self.close(conn, cursor)
        return res

    def update(self, sql, args=None):
        conn, cursor = self.connect()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close(conn, cursor)
        return res

    def delete(self, sql, args=None):
        conn, cursor = self.connect()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close(conn, cursor)
        return res



# test = MySQLHandler()
# test.test_connect()

# sql = '''
#   INSERT INTO financial_cashflow ( `code`, `report_data` ) VALUES ( 600196, 20230331 ),( 600196, 20220331 ),( 600196, 20220630 ),(600196,20221231 )
#   '''
#
# test.insert_one_noargs(sql=sql)

# conn, cursor = test.connect()
# res = cursor.execute(sql)
# conn.commit()
# test.close(conn, cursor)
# print(res)